package edu.gatech.gro.model.dao;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public abstract class DatabaseHandler extends SQLiteOpenHelper {

	protected static final String DATABASE_NAME = "grocery311db";
	protected static final int DATABASE_VERSION = 4;

	protected static final String USER_TABLE = "User";
	private static final String USER_TABLE_CREATE = "CREATE TABLE " + USER_TABLE + " (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, usernameClean TEXT NOT NULL, email TEXT NOT NULL, password TEXT NOT NULL, creationTime INTEGER, lastUpdateTime INTEGER, deleteFlag INTEGER);";

	protected static final String NAMED_LIST_TABLE = "NamedList";
	private static final String NAMED_LIST_TABLE_CREATE = "CREATE TABLE " + NAMED_LIST_TABLE + " (id INTEGER PRIMARY KEY AUTOINCREMENT, userId INTEGER REFERENCES User(id) ON UPDATE CASCADE ON DELETE CASCADE, name TEXT NOT NULL, nameClean TEXT NOT NULL, creationTime INTEGER, lastUpdateTime INTEGER, deleteFlag INTEGER);";

	protected static final String NAMED_LIST_ITEM_TABLE = "NamedListItem";
	private static final String NAMED_LIST_ITEM_TABLE_CREATE = "CREATE TABLE " + NAMED_LIST_ITEM_TABLE + " (namedListId INTEGER REFERENCES NamedList(id) ON UPDATE CASCADE ON DELETE CASCADE, itemId INTEGER REFERENCES Item(id), checked INTEGER, quantity INTEGER, currentQuantity INTEGER, creationTime INTEGER, lastUpdateTime INTEGER, deleteFlag INTEGER);";

	protected static final String GROCERY_STORE_TABLE = "GroceryStore";
	private static final String GROCERY_STORE_TABLE_CREATE = "CREATE TABLE " + GROCERY_STORE_TABLE + " (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, nameClean TEXT NOT NULL, creationTime INTEGER, lastUpdateTime INTEGER, deleteFlag INTEGER);";

	protected static final String ITEM_TYPE_TABLE = "ItemType";
	private static final String ITEM_TYPE_TABLE_CREATE = "CREATE TABLE " + ITEM_TYPE_TABLE + " (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, nameClean TEXT NOT NULL, creationTime INTEGER, lastUpdateTime INTEGER, deleteFlag INTEGER);";

	protected static final String ITEM_TABLE = "Item";
	private static final String ITEM_TABLE_CREATE = "CREATE TABLE " + ITEM_TABLE + " (id INTEGER PRIMARY KEY AUTOINCREMENT, itemTypeId INTEGER REFERENCES ItemType(id), groceryStoreId INTEGER REFERENCES GroceryStore(id), name TEXT NOT NULL, nameClean TEXT NOT NULL, barCode TEXT NOT NULL, barCodeHash TEXT NOT NULL, price REAL, currency INTEGER, creationTime INTEGER, lastUpdateTime INTEGER, deleteFlag INTEGER);";

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(USER_TABLE_CREATE);
		db.execSQL(NAMED_LIST_TABLE_CREATE);
		db.execSQL(GROCERY_STORE_TABLE_CREATE);
		db.execSQL(ITEM_TYPE_TABLE_CREATE);
		db.execSQL(ITEM_TABLE_CREATE);
		db.execSQL(NAMED_LIST_ITEM_TABLE_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + NAMED_LIST_ITEM_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + ITEM_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + ITEM_TYPE_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + GROCERY_STORE_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + NAMED_LIST_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + USER_TABLE);
		onCreate(db);
	}

	public abstract void clear();
}
